package com.rainkaze.IntelliDrawNote.db;

import android.annotation.SuppressLint;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import androidx.annotation.Nullable;

import com.rainkaze.IntelliDrawNote.bean.ChatMessage;
import com.rainkaze.IntelliDrawNote.bean.Notepad;
import com.rainkaze.IntelliDrawNote.bean.TodoNote;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Locale;


// 数据库帮助类
public class MyDbHelper extends SQLiteOpenHelper {

    private static final int version=1;
    private static final String name="IntelliDrawNote.db";

    public MyDbHelper(@Nullable Context context) {
        super(context, name, null, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sqlNote="create table notepad(id integer primary key autoincrement,title text,content text,time text)";
        String sqlTodo="create table todoNote (id integer primary key autoincrement," +
                "content text," +
                "time text," +
                "ddlTime text default null," +
                "status text default '未完成'); ";

        // 新增聊天记录表 chat_history
        String sqlChat = "create table chat_history(" +
                "id integer primary key autoincrement, " +
                "sender text, " +
                "message text, " +
                "time text" +
                ")";
        db.execSQL(sqlNote);
        db.execSQL(sqlTodo);
        db.execSQL(sqlChat);

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    // 插入笔记数据
    public void insertNote(Notepad notepad){
        String sql="insert into notepad(title,content,time) values(?,?,?)";
        SQLiteDatabase db=getWritableDatabase();
        db.execSQL(sql,new String[]{notepad.getTitle(),notepad.getContent(),notepad.getTime()});
//        db.close();
    }

    // 查询所有笔记数据
    @SuppressLint("Range")
    public List<Notepad> findAllNote(){
        SQLiteDatabase db=getReadableDatabase();
        String sql="select * from notepad";
        Cursor cursor=db.rawQuery(sql,null);
        List<Notepad> notepadlist=new ArrayList<>();
        while (cursor.moveToNext()){
            Notepad notepad=new Notepad();
            int id=cursor.getInt(cursor.getColumnIndex("id"));
            String content=cursor.getString(cursor.getColumnIndex("content"));
            String title=cursor.getString(cursor.getColumnIndex("title"));
            String time=cursor.getString(cursor.getColumnIndex("time"));

            notepad.setId(id);
            notepad.setContent(content);
            notepad.setTime(time);
            notepad.setTitle(title);
            notepadlist.add(notepad);
        }
        cursor.close();
//        db.close();
        return notepadlist;
    }

    // 查询包含某字符的笔记数据
    @SuppressLint("Range")
    public List<Notepad> findNote(String keyword) {
        SQLiteDatabase db = getReadableDatabase();
        String sql = "SELECT * FROM notepad WHERE content LIKE ? OR title LIKE ?";
        Cursor cursor = db.rawQuery(sql, new String[]{"%" + keyword + "%", "%" + keyword + "%"});
        List<Notepad> notepadList = new ArrayList<>();

        while (cursor.moveToNext()) {
            Notepad notepad = new Notepad();
            notepad.setId(cursor.getInt(cursor.getColumnIndex("id")));
            notepad.setTitle(cursor.getString(cursor.getColumnIndex("title")));
            notepad.setContent(cursor.getString(cursor.getColumnIndex("content")));
            notepad.setTime(cursor.getString(cursor.getColumnIndex("time")));
            notepadList.add(notepad);
        }
        cursor.close();
//        db.close();
        return notepadList;
    }


    // 删除笔记数据
    public void deleteNote(Notepad notepad) {
        SQLiteDatabase db=getWritableDatabase();
        String sql="delete from notepad where id=?";
        db.execSQL(sql,new Object[]{notepad.getId()});
//        db.close();
    }

    // 更新笔记数据
    public void updateNote(Notepad notepad) {
        SQLiteDatabase db=getWritableDatabase();
        String sql="update notepad set title=?,content=?,time=? where id=?";
        db.execSQL(sql,new Object[]{notepad.getTitle(),notepad.getContent(),notepad.getTime(),notepad.getId()});
//        db.close();
    }

    // 插入待办数据
    public void insertTodo(TodoNote todoNote){
        String sql="insert into todoNote(content,time,ddlTime,status) values(?,?,?,?)";
        SQLiteDatabase db=getWritableDatabase();
        db.execSQL(sql,new String[]{todoNote.getContent(),todoNote.getTime(),todoNote.getDdlTime(),todoNote.getStatus()});
//        db.close();
    }
    // 查询所有待办数据
    @SuppressLint("Range")
    public List<TodoNote> findAllTodo(){
        SQLiteDatabase db = getReadableDatabase();

        // 根据你的 status 类型选择对应的 SQL
        String sql = "SELECT * FROM todoNote ORDER BY CASE status WHEN '未完成' THEN 0 ELSE 1 END, id DESC";

        Cursor cursor = db.rawQuery(sql, null);
        List<TodoNote> todoNoteList = new ArrayList<>();

        while (cursor.moveToNext()){
            TodoNote todoNote = new TodoNote();
            todoNote.setId(cursor.getInt(cursor.getColumnIndex("id")));
            todoNote.setContent(cursor.getString(cursor.getColumnIndex("content")));
            todoNote.setTime(cursor.getString(cursor.getColumnIndex("time")));
            todoNote.setDdlTime(cursor.getString(cursor.getColumnIndex("ddlTime")));
            todoNote.setStatus(cursor.getString(cursor.getColumnIndex("status")));
            todoNoteList.add(todoNote);
        }
        cursor.close();
//        db.close();
        return todoNoteList;
    }

    // 删除待办数据
    public void deleteTodo(TodoNote todoNote) {
        SQLiteDatabase db=getWritableDatabase();
        String sql="delete from todoNote where id=?";
        db.execSQL(sql,new Object[]{todoNote.getId()});
//        db.close();
    }
    // 更新待办数据
    public void updateTodo(TodoNote todoNote) {
        SQLiteDatabase db=getWritableDatabase();
        String sql="update todoNote set content=?,time=?,ddlTime=?,status=? where id=?";
        db.execSQL(sql,new Object[]{todoNote.getContent(),todoNote.getTime(),todoNote.getDdlTime(),todoNote.getStatus(),todoNote.getId()});
//        db.close();
    }

    // 插入聊天记录
    public void insertChatMessage(ChatMessage chatMessage) {
        SQLiteDatabase db = getWritableDatabase();
        String sql = "insert into chat_history (sender, message, time) values(?, ?, ?)";
        // 可以根据需要生成时间戳（这里以当前时间字符串为例）
        String currentTime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.getDefault()).format(new Date());
        String sender = chatMessage.isUserMessage() ? "user" : "ai";
        db.execSQL(sql, new String[]{sender, chatMessage.getMessage(), currentTime});
    }

    // 获取所有聊天记录（按 id 升序排列，即按照聊天顺序）
    @SuppressLint("Range")
    public List<ChatMessage> getChatHistory() {
        SQLiteDatabase db = getReadableDatabase();
        String sql = "select * from chat_history order by id asc";
        Cursor cursor = db.rawQuery(sql, null);
        List<ChatMessage> historyList = new ArrayList<>();
        while (cursor.moveToNext()){
            String sender = cursor.getString(cursor.getColumnIndex("sender"));
            String message = cursor.getString(cursor.getColumnIndex("message"));
            boolean isUserMessage = "user".equals(sender);
            ChatMessage chatMessage = new ChatMessage(message, isUserMessage);
            historyList.add(chatMessage);
        }
        cursor.close();
        return historyList;
    }
}
